package com.zhu.tool_test.util;


import java.io.InputStream;
import java.io.OutputStream;
import java.util.*;
import java.util.stream.Collectors;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.*;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.hitotek.util.TopDateUtil;
import com.zhu.tool_test.entity.Device;
import core.MultipartData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import util.DateUtils;

/**
 * easyexcel工具类 -- Excel解析
 *
 * @author Lynch
 */
@Slf4j
public class EasyExcelUtil {

    private static LinkedList<String> codeList = new LinkedList<>();

    /**
     * StringList 解析监听器
     */
    private static class StringExcelListener extends AnalysisEventListener {
        /**
         * 自定义用于暂时存储data
         * 可以通过实例获取该值
         */
        private List<List<String>> datas = new ArrayList<>();

        /**
         * 每解析一行都会回调invoke()方法
         *
         * @param object
         * @param context
         */
        @Override
        public void invoke(Object object, AnalysisContext context) {
            List<String> stringList= (List<String>) object;
            //数据存储到list，供批量处理，或后续自己业务逻辑处理。
            datas.add(stringList);
            //根据自己业务做处理
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            //解析结束销毁不用的资源
            //注意不要调用datas.clear(),否则getDatas为null
        }

        public List<List<String>> getDatas() {
            return datas;
        }
        public void setDatas(List<List<String>> datas) {
            this.datas = datas;
        }
    }

    /**
     * 使用 StringList 来读取Excel
     * @param inputStream Excel的输入流
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     * @return 返回 StringList 的列表
     */
    public static List<List<String>> readExcelWithStringList(InputStream inputStream,ExcelTypeEnum excelTypeEnum) {
        StringExcelListener listener = new StringExcelListener();
        ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        excelReader.read();
        return  listener.getDatas();
    }

    /**
     * 使用 StringList 来写入Excel
     * @param outputStream Excel的输出流
     * @param data 要写入的以StringList为单位的数据
     * @param table 配置Excel的表的属性
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     */
    public static void writeExcelWithStringList(OutputStream outputStream, List<List<String>> data, Table table,ExcelTypeEnum excelTypeEnum) {
        //这里指定不需要表头，因为String通常表头已被包含在data里
        ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,false);
        //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系,无表头
        Sheet sheet1 = new Sheet(0, 0);
        writer.write0(data, sheet1,table);
        writer.finish();
    }

    /**
     * 模型解析监听器 -- 每解析一行会回调invoke()方法，整个excel解析结束会执行doAfterAllAnalysed()方法
     */
    private static class ModelExcelListener<E> extends AnalysisEventListener<E> {
        private List<E> dataList = new ArrayList<E>();

        @Override
        public void invoke(E object, AnalysisContext context) {
            dataList.add(object);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
        }

        public List<E> getDataList() {
            return dataList;
        }

        @SuppressWarnings("unused")
        public void setDataList(List<E> dataList) {
            this.dataList = dataList;
        }
    }

    /**
     * 使用 模型 来读取Excel
     *
     * @param inputStream Excel的输入流
     * @param clazz 模型的类
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     *
     * @return 返回 模型 的列表
     */
    public static <E> List<E> readExcelWithModel(InputStream inputStream, Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) {
        // 解析每行结果在listener中处理
        ModelExcelListener<E> listener = new ModelExcelListener<E>();
        ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        //默认只有一列表头
        excelReader.read(new Sheet(1, 1, clazz));

        return listener.getDataList();
    }

    /**
     * 使用 模型 来写入Excel
     *
     * @param outputStream Excel的输出流
     * @param data 要写入的以 模型 为单位的数据
     * @param table 配置Excel的表的属性
     * @param clazz 模型的类
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     */
    public static void writeExcelWithModel(OutputStream outputStream, List<? extends BaseRowModel> data,
                                           Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum)  {
        //这里指定需要表头，因为model通常包含表头信息
        ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);
        //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
        Sheet sheet1 = new Sheet(1, 0, clazz);
        writer.write(data, sheet1);
        writer.finish();
    }

    public static void dynamicHeadWrite(OutputStream outputStream, List<List<String>> head, List<List<String>> body,ExcelTypeEnum excelTypeEnum){

        // 设置样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)13);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        // 导出表格
        EasyExcel.write(outputStream,null)
                .registerWriteHandler(new CustomCellWriteHandler()) // 自动列宽
                .registerWriteHandler(horizontalCellStyleStrategy) // 设置样式
                //.file(outputStream)
                .head(head)
                .sheet("模板")
                .doWrite(body);
    }


    public static List<List<String>> jsonBody(){
        // 查询数据
        Device device = new Device();
        List<Device> devices = device.selectAll();
        // 转换成List<map>
        List<MultipartData> multipartDataList = MultipartData.trans2MultipartData(devices);
        // 选择要取出的部分
        List<List<String>> bodies = multipartDataList.stream().map(data -> {
            LinkedList<String> list = new LinkedList<>();
            codeList.stream().forEach(code -> {
                        // 对时间特殊处理
                        if ("sampleTime".equals(code)) {
                            String sampleTime = DateUtils.format(data.getDatePart(code), TopDateUtil.YYYY_MM_DD_HH_MM_SS);
                            list.add(sampleTime);
                            return;
                        }
                        // 对null值进行特殊处理
                        if (null == data.get(code)) {
                            list.add("-");
                            return;
                        }
                        list.add(String.valueOf(data.get(code)));
                    }

            );
            return list;
        }).collect(Collectors.toList());

        return bodies;
    }


    public static List<List<String>> jsonHead(String json){

        // 清除codeList
        codeList.clear();

//        String json = "{\"head\":[\n" +
//                "\t[{\"code\":\"id\",\"name\":\"id\"}],\n" +
//                "\t[{\"code\":\"mn\",\"name\":\"编码\"}],\n" +
//                "\t[{\"code\":\"\",\"name\":\"因子信息\"},{\"code\":\"polName\",\"name\":\"因子编码\"}],\n" +
//                "\t[{\"code\":\"\",\"name\":\"因子信息\"},{\"code\":\"factorId\",\"name\":\"因子id\"}],\n" +
//                "\t[{\"code\":\"alarmInfo\",\"name\":\"告警信息\"}],\n" +
//                "\t[{\"code\":\"flag\",\"name\":\"状态\"}],\n" +
//                "\t[{\"code\":\"sampleTime\",\"name\":\"时间\"}]\n" +
//                "]}";
        ObjectMapper objectMapper = new ObjectMapper();
        JsonNode jsonNode = null;
        try {
            jsonNode = objectMapper.readTree(json);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
        // 打印list
        log.info("jsonNode:{}",jsonNode);
        // 转换成 list<list<String>>
        LinkedList<List<String>> outList = new LinkedList<>();
        JsonNode head = jsonNode.get("head");
        // 判断表头是否为数组
        if(null != head && head.isArray()) {
            // 循环遍历数组里的表头数据
            for (int j=0;j<head.size();j++) {
                JsonNode nodeList = head.get(j);
                // 创建list
                LinkedList<String> list = new LinkedList<>();
                for (int i = 0; i < nodeList.size(); i++) {
                    JsonNode node = nodeList.get(i);
                    // 如果node为null
                    if (null == node || node.size() == 0) {
                        continue;
                    }
                    log.info("node:{}", node);
                    String code = node.get("code").asText();
                    String name = node.get("name").asText();

                    list.add(name);
                    // 将标头的code保存下来
                    if (null == code || "".equals(code)) {
                        continue;
                    }
                    codeList.add(code);
                }
                // 最后保存成head需要的list<List<String>>
                outList.add(list);
            }

        } else if (null == head) {
            log.info("head为空");
        }
        log.info("outList:{}",outList);
        return outList;
    }




    /**
     * @author jamin
     * @date 2020/7/29 11:28
     * 设置自适应列宽
     */
    public static class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
        private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
            boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
            if (needSetWidth) {
                Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
                if (maxColumnWidthMap == null) {
                    maxColumnWidthMap = new HashMap<>();
                    CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
                }

                Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
                if (columnWidth >= 0) {
                    if (columnWidth > 255) {
                        columnWidth = 255;
                    }

                    Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                    }

                }
            }
        }

        private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
            if (isHead) {
                return cell.getStringCellValue().getBytes().length;
            } else {
                CellData cellData = cellDataList.get(0);
                CellDataTypeEnum type = cellData.getType();
                if (type == null) {
                    return -1;
                } else {
                    switch (type) {
                        case STRING:
                            return cellData.getStringValue().getBytes().length;
                        case BOOLEAN:
                            return cellData.getBooleanValue().toString().getBytes().length;
                        case NUMBER:
                            return cellData.getNumberValue().toString().getBytes().length;
                        default:
                            return -1;
                    }
                }
            }
        }
    }

}
